/**
 *
 */
package com.audi.box.adapter;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.audi.dto.DeviceItem;
import com.audi.dto.HistoryActionSmartTriggerDto;
import com.audi.dto.HistoryChangePlaceOfDevice;
import com.audi.dto.HistoryDeviceItemAddRemove;
import com.audi.dto.HistoryItem;
import com.audi.dto.HistoryMemberAction;
import com.audi.dto.HistoryMemberItem;
import com.audi.dto.HistoryPlaceItem;
import com.audi.dto.HistoryPlaceItemAddRemove;
import com.audi.dto.HistoryRenamePlaceOrChangeCapability;
import com.audi.dto.HistorySceneItemAddRemove;
import com.audi.dto.HistoryWarningSensorsTrigger;
import com.audi.dto.HistoryWifimatchHome;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * @author HuyPVA
 * 
 */
public class SQLiteAsusHelperHistory extends SQLiteAsusHelperCommon {
    
    /**
     * @param context
     */
    public SQLiteAsusHelperHistory(Context context) {
        super(context);
    }
    
    // get List History action of Device
    public ArrayList<HistoryDeviceItemAddRemove> getListHistoryDeviceAddRemove() {
        Log.d("SQLite Helper History", "getListHistoryDeviceAddRemove");
        ArrayList<HistoryDeviceItemAddRemove> listDevice = new ArrayList<HistoryDeviceItemAddRemove>();
        
        String sql = "SELECT "
                + TABLE_DEVICE + "." + KEY_DEVICE_NAME + ","
                
                + TABLE_HISTORY_DEVICE + "." + KEY_HISTORY_DEVICE_TYPE + ","
                + TABLE_HISTORY_DEVICE + "." + KEY_HISTORY_DEVICE_DATETIME + ","
                
                + TABLE_USER + "." + KEY_USER_NAME + " , "
                + TABLE_DEVICE + "." + KEY_DEVICE_TYPE + ","
                + TABLE_HISTORY_DEVICE + "." + KEY_HISTORY_DEVICE_TYPE + " , "
                + TABLE_PLACE + "." + KEY_PLACE_NAME
                
                + " FROM "
                + TABLE_HISTORY_DEVICE + " , "
                + TABLE_USER + " , "
                + TABLE_DEVICE + " , "
                + TABLE_PLACE
                
                + " WHERE "
                + TABLE_HISTORY_DEVICE + "." + KEY_HISTORY_DEVICE_USER_ID
                + " = "
                + TABLE_USER + "." + KEY_USER_ID
                
                + " AND  "
                + TABLE_HISTORY_DEVICE + "." + KEY_HISTORY_DEVICE_DEVICE_ID
                + " = "
                + TABLE_DEVICE + "." + KEY_DEVICE_ID
                
                + " AND  "
                + TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID
                + " = "
                + TABLE_PLACE + "." + KEY_PLACE_ID
                
                + " ORDER BY "
                + TABLE_HISTORY_DEVICE + "." + KEY_HISTORY_DEVICE_DATETIME + " DESC ";
        
        sql += "";
        
        /*
         * String selectQuery = "SELECT * FROM "+ TABLE_DEVICE+ " WHERE "+
         * KEY_DEVICE_PLACE_ID+ "="+ placeId; String selectQuery2 = "SELECT "+
         * KEY_PLACE_NAME+ " FROM "+ TABLE_PLACE+ " WHERE "+ KEY_PLACE_ID+ "="+
         * placeId;
         */
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        // Cursor curso2 = db.rawQuery(selectQuery2, null);
        try {
            if (curso.moveToFirst() /* && curso2.moveToFirst() */) {
                do {
                    HistoryDeviceItemAddRemove de = new HistoryDeviceItemAddRemove();
                    
                    de.setDeviceName(curso.getString(0).toString());
                    de.setDeviceType(Integer.parseInt(curso.getString(1).toString()));
                    de.setDateTime(curso.getString(2));
                    de.setUserName(curso.getString(3).toString());
                    de.setDeviceType(Integer.parseInt(curso.getString(4).toString()));
                    de.setDeviceTypeType(Integer.parseInt(curso.getString(5).toString()));
                    de.setPlaceName(curso.getString(6).toString());
                    listDevice.add(de);
                    
                } while (curso.moveToNext());
                
            }
            
        } catch (Exception e) {
        }
        
        return listDevice;
    }
    
    /**
     * Add HistoryItem
     * 
     * @param history
     *            HistoryItem
     * @return 1 if successful, 0 if device exist
     */
    public int addHistory(HistoryItem history) {
        Log.d("SQLite Helper History", "addHistory(HistoryItem)");
        
        Long dateTime = System.currentTimeMillis();
        history.setDateTime(dateTime);
        
        ContentValues values = convertHistoryItemToContentValue(history);
        
        return sqlInsert(TABLE_HISTORY, null, values);
    }
    
    /**
     * Add History From User Action.
     * 
     * @param action
     *            user action
     * @param target
     *            action on target (device/place/scene/member/trigger)
     * @param targetId
     *            Device Id, Place Id, Scene Id, Member Id, Smart Trigger Id
     * @param source
     *            Target value before action make
     * @param destination
     *            Target value after action make
     * @param description
     *            Action description
     * @param userId
     *            User Id
     * @return 1 if successful, 0 if failed
     */
    private int addHistoryUser(String action,
            String target, int targetId,
            String source, String destination,
            String description, int userId) {
        
        Log.d("SQLite Helper History", "addHistoryUser");
        
        HistoryItem history = new HistoryItem();
        
        history.setTarrgetId(targetId);
        history.setTarrgetType(target);
        history.setTarrgetAction(action);
        if (source != null && !source.equals("")) {
            history.setTarrgetSource(source);
        }
        
        if (destination != null && !destination.equals("")) {
            history.setTarrgetSource(destination);
        }
        
        history.setDescription(description);
        history.setUserId(userId);
        
        return addHistory(history);
    }
    
    /**
     * Add history on device action.
     * 
     * @param action
     *            Device action (Add/Delete/Update/Rename/Change Place)
     * @param deviceId
     *            Device target
     * @param source
     *            Device value before action make
     * @param destination
     *            Device value after action make
     * @param description
     *            Action description
     * @param userId
     *            User Id
     * @return 1 if successful, 0 if failed
     */
    public int addHistoryDevice(String action, int deviceId,
            String source, String destination,
            String description, int userId) {
        Log.d("SQLite Helper History", "addHistoryDevice");
        
        return addHistoryUser(action, "device", deviceId, source, destination, description, userId);
    }
    
    /**
     * Add history on place action.
     * 
     * @param action
     *            Place action (Add/Delete/Update/Rename/Change Capability)
     * @param placeId
     *            Place target
     * @param source
     *            Place value before action make
     * @param destination
     *            Place value after action make
     * @param description
     *            Action description
     * @param userId
     *            User Id
     * @return 1 if successful, 0 if failed
     */
    public int addHistoryPlace(String action, int placeId,
            String source, String destination,
            String description, int userId) {
        Log.d("SQLite Helper History", "addHistoryPlace");
        
        return addHistoryUser(action, "place", placeId, source, destination, description, userId);
    }
    
    /**
     * Add history on place action.
     * 
     * @param action
     *            Scene action (Add/Delete/Update/Rename/Change Capability)
     * @param sceneId
     *            Scene target
     * @param source
     *            Scene value before action make
     * @param destination
     *            Scene value after action make
     * @param description
     *            Action description
     * @param userId
     *            User Id
     * @return 1 if successful, 0 if failed
     */
    public int addHistoryScene(String action, int sceneId,
            String source, String destination,
            String description, int userId) {
        Log.d("SQLite Helper History", "addHistoryScene");
        
        return addHistoryUser(action, "scene", sceneId, source, destination, description, userId);
    }
    
    /**
     * Add history on member action.
     * 
     * @param action
     *            Member action (Add/Delete/Update/Rename/Change Capability)
     * @param memberId
     *            Member target
     * @param source
     *            Member value before action make
     * @param destination
     *            Member value after action make
     * @param description
     *            Action description
     * @param userId
     *            User Id
     * @return 1 if successful, 0 if failed
     */
    public int addHistoryMember(String action, int memberId,
            String source, String destination,
            String description, int userId) {
        Log.d("SQLite Helper History", "addHistoryScene");
        
        return addHistoryUser(action, "member", memberId, source, destination, description, userId);
    }
    
    /**
     * Add history on smartTrigger action.
     * 
     * @param action
     *            SmartTrigger action (Add/Delete/Update/Rename/Change
     *            Capability)
     * @param smartTriggerId
     *            SmartTrigger target
     * @param source
     *            SmartTrigger value before action make
     * @param destination
     *            SmartTrigger value after action make
     * @param description
     *            Action description
     * @param userId
     *            User Id
     * @return 1 if successful, 0 if failed
     */
    public int addHistorySmartTrigger(String action, int smartTriggerId,
            String source, String destination,
            String description, int userId) {
        Log.d("SQLite Helper History", "addHistoryScene");
        
        return addHistoryUser(action, "smartTrigger", smartTriggerId, source, destination,
                description, userId);
    }
    
    // add data to History Device add/remove
    public int addDataToHistoryDevice(HistoryDeviceItemAddRemove item) {
        Log.d("SQLite Helper History", "addDataToHistoryDevice");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_DEVICE_DEVICE_ID, item.getDeviceId());
        values.put(KEY_HISTORY_DEVICE_USER_ID, item.getUserId());
        values.put(KEY_HISTORY_DEVICE_TYPE, item.getDeviceType());
        values.put(KEY_HISTORY_DEVICE_DATETIME, item.getDateTime());
        
        return sqlInsert(TABLE_HISTORY_DEVICE, null, values);
    }
    
    public void updateHistoryDevice(int id, int userId, int type_history_device) {
        Log.d("SQLite Helper History", "updateHistoryDevice");
        
        if (id > 0) {
            HistoryDeviceItemAddRemove itemHisDevice = new HistoryDeviceItemAddRemove();
            itemHisDevice.setDeviceId(id);
            itemHisDevice.setUserId(userId);
            itemHisDevice.setDeviceType(type_history_device);
            String mydate = String.valueOf(System.currentTimeMillis());
            itemHisDevice.setDateTime(mydate);
            addDataToHistoryDevice(itemHisDevice);
        }
    }
    
    public int addHistoryPlace(HistoryPlaceItem item) {
        Log.d("SQLite Helper History", "addHistoryPlace");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_PLACE_PLACE_ID, item.getHistoryPlacePlaceId());
        values.put(KEY_HISTORY_PLACE_TYPE, item.getHistoryPlaceType());
        values.put(KEY_HISTORY_PLACE_USER_ID, item.getHistoryPlaceUserId());
        values.put(KEY_HISTORY_PLACE_DATETIME, item.getHistoryPlaceDate());
        
        return sqlInsert(TABLE_HISTORY_PLACE, null, values);
    }
    
    private void UpdateHistoryScene(int idScene, int userId, int typeScene) {
        Log.d("SQLite Helper History", "UpdateHistoryScene");
        // TODO Auto-generated method stub
        if (idScene > 0) {
            
            HistorySceneItemAddRemove itemHisScene = new HistorySceneItemAddRemove();
            itemHisScene.setsceneId(idScene);
            itemHisScene.setUserId(userId);
            itemHisScene.setsceneType(typeScene);
            
            String mydate = String.valueOf(System.currentTimeMillis());
            itemHisScene.setDateTime(mydate);
            
            addHistorySceneAddRemove(itemHisScene);
        }
    }
    
    // add history place new/remove
    public int addHistoryPlaceAddRemove(HistoryPlaceItemAddRemove item) {
        Log.d("SQLite Helper History", "addHistoryPlaceAddRemove");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_PLACE_PLACE_ID, item.getPlacePlaceId());
        values.put(KEY_HISTORY_PLACE_TYPE, item.getPlaceType());
        values.put(KEY_HISTORY_PLACE_USER_ID, item.getUserId());
        values.put(KEY_HISTORY_PLACE_DATETIME, item.getDateTime());
        
        return sqlInsert(TABLE_HISTORY_PLACE, null, values);
    }
    
    // get array list History Place
    // getListHistoryPlaceAddRemove
    public ArrayList<HistoryPlaceItemAddRemove> getListHistoryPlaceAddRemove() {
        Log.d("SQLite Helper History", "getListHistoryPlaceAddRemove");
        ArrayList<HistoryPlaceItemAddRemove> listDevice = new ArrayList<HistoryPlaceItemAddRemove>();
        
        String sql = "SELECT "
                
                + TABLE_USER + "." + KEY_USER_NAME + "," + TABLE_PLACE + "." + KEY_PLACE_NAME
                + " , "
                + TABLE_HISTORY_PLACE + "." + KEY_HISTORY_PLACE_TYPE + "," + TABLE_HISTORY_PLACE
                + "." + KEY_HISTORY_PLACE_DATETIME + " FROM " + TABLE_HISTORY_PLACE + " , "
                + TABLE_USER + " , " + TABLE_PLACE + " where "
                
                + TABLE_HISTORY_PLACE + "." + KEY_HISTORY_PLACE_PLACE_ID + " = " + TABLE_PLACE
                + "." + KEY_PLACE_ID + " AND  " + TABLE_HISTORY_PLACE + "."
                + KEY_HISTORY_PLACE_USER_ID + " = "
                
                + TABLE_USER + "." + KEY_USER_ID + " order by " + TABLE_HISTORY_PLACE + "."
                + KEY_HISTORY_PLACE_DATETIME + " DESC ";
        
        sql += "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        // Cursor curso2 = db.rawQuery(selectQuery2, null);
        try {
            if (curso.moveToFirst() /* && curso2.moveToFirst() */) {
                do {
                    HistoryPlaceItemAddRemove de = new HistoryPlaceItemAddRemove();
                    
                    de.setUserName(curso.getString(0).toString());
                    de.setPlaceName(curso.getString(1).toString());
                    de.setPlaceType(Integer.parseInt(curso.getString(2)));
                    de.setDateTime(curso.getString(3));
                    listDevice.add(de);
                    
                } while (curso.moveToNext());
                
            }
            
        } catch (Exception e) {
        }
        
        return listDevice;
    }
    
    // get List History Scene
    public ArrayList<HistorySceneItemAddRemove> getListHistorySceneAddRemove() {
        Log.d("SQLite Helper History", "getListHistorySceneAddRemove");
        ArrayList<HistorySceneItemAddRemove> listDevice = new ArrayList<HistorySceneItemAddRemove>();
        
        String sql = "SELECT " + TABLE_HISTORY_SCENE + "." + KEY_HISTORY_SCENE_TYPE + ","
                + TABLE_USER + "." + KEY_USER_NAME + "," + TABLE_SCENE + "." + KEY_SCENE_NAME + ","
                + TABLE_PLACE + "." + KEY_PLACE_NAME + " , " + TABLE_HISTORY_SCENE + "."
                + KEY_HISTORY_SCENE_DATETIME + " FROM " + TABLE_HISTORY_SCENE + " , " + TABLE_USER
                + " , " + TABLE_SCENE + " , " + TABLE_PLACE + " where " + TABLE_HISTORY_SCENE + "."
                + KEY_HISTORY_SCENE_USER_ID + " = " + TABLE_USER + "." + KEY_USER_ID +
                
                " AND  " + TABLE_HISTORY_SCENE + "." + KEY_HISTORY_SCENE_SCENE_ID + " = "
                + TABLE_SCENE + "." + KEY_SCENE_ID + " AND " + TABLE_PLACE + "." + KEY_PLACE_ID
                + " = " + TABLE_SCENE + "." + KEY_SCENE_PLACE_ID + " order by "
                + TABLE_HISTORY_SCENE + "." + KEY_HISTORY_SCENE_DATETIME + " DESC ";
        ;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        // Cursor curso2 = db.rawQuery(selectQuery2, null);
        try {
            if (curso.moveToFirst() /* && curso2.moveToFirst() */) {
                do {
                    HistorySceneItemAddRemove de = new HistorySceneItemAddRemove();
                    
                    de.setsceneType(Integer.parseInt(curso.getString(0).toString()));
                    de.setUserName(curso.getString(1).toString());
                    de.setsceneName(curso.getString(2).toString());
                    de.setPlaceName(curso.getString(3).toString());
                    de.setDateTime(curso.getString(4).toString());
                    
                    listDevice.add(de);
                    
                } while (curso.moveToNext());
                
            }
            
        } catch (Exception e) {
        }
        
        return listDevice;
    }
    
    // add history scene new/remove
    public int addHistorySceneAddRemove(HistorySceneItemAddRemove item) {
        Log.d("SQLite Helper History", "addHistorySceneAddRemove");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_SCENE_TYPE, item.getsceneType());
        values.put(KEY_HISTORY_SCENE_SCENE_ID, item.getsceneId());
        values.put(KEY_HISTORY_SCENE_USER_ID, item.getUserId());
        values.put(KEY_HISTORY_SCENE_DATETIME, item.getDateTime());
        
        return sqlInsert(TABLE_HISTORY_SCENE, null, values);
    }
    
    // add history change place of device
    public int addHistoryChangePlaceOfDevice(HistoryChangePlaceOfDevice item) {
        Log.d("SQLite Helper History", "addHistoryChangePlaceOfDevice");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DEVICE_ID, item.getDeviceId());
        values.put(KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID, item.getPlaceId());
        values.put(KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID, item.getMemberChangeId());
        values.put(KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DATETIME, item.getDateTimeAdd());
        
        return sqlInsert(TABLE_HISTORY_MOVE_PLACE_OF_DEVICE, null, values);
    }
    
    // add history rename place or change capability
    public int addHistoryRenameOrChangeCapabilityOfPlace(HistoryRenamePlaceOrChangeCapability item) {
        Log.d("SQLite Helper History", "addHistoryRenameOrChangeCapabilityOfPlace");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_PLACE_ID, item.getPlaceId());
        values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID, item.getMemberId());
        values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_FROM, item.getRenameFrom());
        values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_TO, item.getRenameTo());
        values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_FROM, item.getCapabilityFrom());
        values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_TO, item.getCapabilityTo());
        values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_DATETIME, item.getTimeAdd());
        
        return sqlInsert(TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE, null, values);
    }
    
    // get list HistoryChangePlaceOfDevice
    public ArrayList<HistoryChangePlaceOfDevice> getListHistoryChangePlaceOfDevice() {
        Log.d("SQLite Helper History", "getListHistoryChangePlaceOfDevice");
        ArrayList<HistoryChangePlaceOfDevice> listhistory = new ArrayList<HistoryChangePlaceOfDevice>();
        
        String sql = "SELECT " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID + " , " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE
                + "." + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DEVICE_ID + ","
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID + " , "
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID + " , " + TABLE_PLACE + "."
                + KEY_PLACE_NAME + " , " + TABLE_USER + "." + KEY_USER_NAME + " , " + TABLE_DEVICE
                + "." + KEY_DEVICE_NAME + " ,count(" + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID + ")"
                
                + " FROM " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + " , " + TABLE_USER + " , "
                + TABLE_PLACE + " , " + TABLE_DEVICE + " where "
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID + " = " + TABLE_USER + "." + KEY_USER_ID
                + " and " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID + " = " + TABLE_PLACE + "."
                + KEY_PLACE_ID + " and " + TABLE_DEVICE + "." + KEY_DEVICE_ID + "="
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "." + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID
                + " GROUP BY " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DATETIME + " order by "
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DATETIME + " DESC";
        sql += "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        if (curso.moveToFirst()) {
            do {
                HistoryChangePlaceOfDevice item = new HistoryChangePlaceOfDevice();
                item.setHistoryChangePlaceOfDeviceId(curso.getInt(0));
                item.setDeviceId(curso.getInt(1));
                item.setPlaceId(curso.getInt(2));
                item.setMemberChangeId(curso.getInt(3));
                item.setPlaceName(curso.getString(4));
                item.setMemberChange(curso.getString(5));
                item.setDeviceName(curso.getString(6));
                item.setCountDeviceChange(curso.getInt(7));
                listhistory.add(item);
            } while (curso.moveToNext());
        }
        return listhistory;
    }
    
    // get list HistoryRenamePlaceOrChangeCapability
    public ArrayList<HistoryRenamePlaceOrChangeCapability>
            getListHistoryRenamePlaceOrChangeCapability() {
        Log.d("SQLite Helper History", "getListHistoryRenamePlaceOrChangeCapability");
        ArrayList<HistoryRenamePlaceOrChangeCapability> listHistory = new ArrayList<HistoryRenamePlaceOrChangeCapability>();
        String sql = "SELECT " + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_PLACE_ID + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_FROM + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_TO + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_FROM + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_TO + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_DATETIME + " , " + TABLE_USER + "."
                + KEY_USER_NAME + " , " + TABLE_PLACE + "." + KEY_PLACE_NAME;
        
        sql += " FROM " + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + " , " + TABLE_USER + " , "
                + TABLE_PLACE + " where " + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID + " = " + TABLE_PLACE + "."
                + KEY_PLACE_ID + " and " + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID + " = " + TABLE_USER + "."
                + KEY_USER_ID + " order by " + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        try {
            if (curso.moveToFirst()) {
                do {
                    HistoryRenamePlaceOrChangeCapability item = new HistoryRenamePlaceOrChangeCapability();
                    item.setHistoryRenamePlaceOrChangeCapabilityID(curso.getInt(0));
                    item.setPlaceId(curso.getInt(1));
                    item.setMemberId(curso.getInt(2));
                    item.setRenameFrom(curso.getString(3));
                    item.setRenameTo(curso.getString(4));
                    item.setCapabilityFrom(curso.getInt(5));
                    item.setCapabilityTo(curso.getInt(6));
                    item.setTimeAdd(curso.getString(7));
                    item.setMemberName(curso.getString(8));
                    item.setPlaceName(curso.getString(9));
                    listHistory.add(item);
                } while (curso.moveToNext());
            }
        } catch (Exception e) {
        }
        return listHistory;
    }
    
    public void updateHistoryPlace(int idPlace, int userId, int typeHistoryPlace) {
        Log.d("SQLite Helper History", "typeHistoryPlace");
        
        if (idPlace > 0) {
            HistoryPlaceItemAddRemove item_history = new HistoryPlaceItemAddRemove();
            item_history.setPlacePlaceId(idPlace);
            item_history.setPlaceType(typeHistoryPlace); // its up to action.
            item_history.setUserId(userId); // get current user!
            String mydate = String.valueOf(System.currentTimeMillis());
            item_history.setDateTime(mydate);
            
            addHistoryPlaceAddRemove(item_history);
        }
    }
    
    // add history action smart
    public int addHistoryActionSmartTrigger(HistoryActionSmartTriggerDto item) {
        Log.d("SQLite Helper History", "addHistoryActionSmartTrigger");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_SMART_TRIGGER_WHEN_TRIGGER_ID, item.getWhenId());
        values.put(KEY_HISTORY_SMART_TRIGGER_TYPE, item.getType());
        values.put(KEY_HISTORY_SMART_TRIGGER_USER_ID, item.getUserId());
        values.put(KEY_HISTORY_SMART_TRIGGER_DATETIME, item.getDate());
        
        return sqlInsert(TABLE_HISTORY_SMART_TRIGGER, null, values);
    }
    
    // get list history action smart trigger
    public ArrayList<HistoryActionSmartTriggerDto> getListActionSmartTrigger() {
        Log.d("SQLite Helper History", "getListActionSmartTrigger");
        ArrayList<HistoryActionSmartTriggerDto> listMember = new ArrayList<HistoryActionSmartTriggerDto>();
        
        String selectQuery = " SELECT " + KEY_HISTORY_SMART_TRIGGER_TYPE + ", "
                + KEY_HISTORY_SMART_TRIGGER_DATETIME + "," + KEY_USER_NAME + " FROM "
                + TABLE_HISTORY_SMART_TRIGGER + " , " + TABLE_USER
                
                + " WHERE " + TABLE_HISTORY_SMART_TRIGGER + "." + KEY_HISTORY_SMART_TRIGGER_USER_ID
                + " = " + TABLE_USER + "." + KEY_USER_ID;
        selectQuery += " ORDER BY " + TABLE_HISTORY_SMART_TRIGGER + "."
                + KEY_HISTORY_SMART_TRIGGER_DATETIME + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    HistoryActionSmartTriggerDto itemMember = new HistoryActionSmartTriggerDto();
                    itemMember.setType(Integer.parseInt(cursorSqliteAsusHelper.getString(0)));
                    itemMember.setDate(cursorSqliteAsusHelper.getString(1));
                    itemMember.setUserName(cursorSqliteAsusHelper.getString(2));
                    
                    listMember.add(itemMember);
                    
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return listMember;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    // get list HistoryWarningSensorsTrigger
    public ArrayList<HistoryWarningSensorsTrigger> getListHistoryWarningSensorsTrigger() {
        Log.d("SQLite Helper History", "getListHistoryWarningSensorsTrigger");
        ArrayList<HistoryWarningSensorsTrigger> listDevice = new ArrayList<HistoryWarningSensorsTrigger>();
        
        String sql = "SELECT " + TABLE_HISTORY_WARNING + "." + KEY_HISTORY_WARNING_ID + " , "
                + TABLE_DEVICE + "." + KEY_DEVICE_ID + "," + TABLE_DEVICE + "." + KEY_DEVICE_NAME
                + " , " + TABLE_HISTORY_WARNING + "." + KEY_HISTORY_WARNING_DATETIME + " FROM "
                + TABLE_HISTORY_WARNING + " , " + TABLE_DEVICE + " where " + TABLE_DEVICE + "."
                + KEY_DEVICE_ID + " = " + TABLE_HISTORY_WARNING + "."
                + KEY_HISTORY_WARNING_DEVICE_ID + " order by " + TABLE_HISTORY_WARNING + "."
                + KEY_HISTORY_WARNING_ID + " DESC";
        sql += "";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        try {
            if (curso.moveToFirst()) {
                do {
                    HistoryWarningSensorsTrigger node = new HistoryWarningSensorsTrigger();
                    node.setHistoryWarningSensorsId(curso.getInt(0));
                    node.setSensorName(curso.getString(2));
                    node.setDateTimeAdd(curso.getString(3));
                    listDevice.add(node);
                } while (curso.moveToNext());
            }
        } catch (Exception e) {
        }
        return listDevice;
    }
    
    public void
            updateHistorySmartTrigger(int whenTriggerId, int userId, int typeHistorySmartTrigger) {
        Log.d("SQLite Helper History", "updateHistorySmartTrigger");
        
        if (whenTriggerId > 0) {
            HistoryActionSmartTriggerDto item_history = new HistoryActionSmartTriggerDto();
            item_history.setWhenId(whenTriggerId);
            item_history.setType(typeHistorySmartTrigger); // its up to action.
            item_history.setUserId(userId); // get current user!
            String mydate = String.valueOf(System.currentTimeMillis());
            item_history.setDate(mydate);
            addHistoryActionSmartTrigger(item_history);
        }
    }
    
    // add history Warning sensors trigger
    public int addHistoryWarningSensorsTrigger(HistoryWarningSensorsTrigger item) {
        Log.d("SQLite Helper History", "addHistoryWarningSensorsTrigger");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_WARNING_DEVICE_ID, item.getSensorId());
        values.put(KEY_HISTORY_WARNING_DATETIME, item.getDateTimeAdd());
        
        return sqlInsert(TABLE_HISTORY_WARNING, null, values);
    }
    
    // add data history hume user
    public int updateHistoryMemberAction(HistoryMemberItem item) {
        Log.d("SQLite Helper History", "updateHistoryMemberAction");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_USER_HOME_USER_ID, item.getHistoryMemberMemberId());
        values.put(KEY_HISTORY_USER_HOME_TYPE, item.getHistoryMemberType());
        values.put(KEY_HISTORY_USER_HOME_DATETIME, item.getHistoryMemberDate());
        
        return sqlInsert(TABLE_HISTORY_USER_HOME, null, values);
    }
    
    /*
     * public ArrayList<HistoryWifimatchHome> getListWifiHistory() {
     * SQLiteDatabase sql = this.getReadableDatabase();
     * ArrayList<HistoryWifimatchHome> listWifiHistory = new
     * ArrayList<HistoryWifimatchHome>(); String querysql = " SELECT "+
     * TABLE_BOX+ "."+ KEY_BOX_NAME+ "," + TABLE_HISTORY_BOX_WIFI+ "."+
     * KEY_HISTORY_BOX_WIFI_DATETIME + ","+ TABLE_HISTORY_BOX_WIFI+ "." +
     * KEY_HISTORY_BOX_WIFI_WIFI_ID+ ","+ TABLE_USER+ "." + KEY_USER_ICON+ ","+
     * TABLE_USER+ "."+ KEY_USER_NAME+ "," + TABLE_WIFI+ "."+ KEY_WIFI_NAME+
     * " FROM "
     * 
     * + TABLE_HISTORY_BOX_WIFI+ ","+ TABLE_BOX+ ","+ TABLE_USER + ","+
     * TABLE_WIFI+ " WHERE "
     * 
     * + TABLE_HISTORY_BOX_WIFI+ "."+ KEY_HISTORY_BOX_WIFI_WIFI_ID + "="+
     * TABLE_BOX+ "."+ KEY_BOX_WIFI_ID+ " AND " + TABLE_WIFI+ "."+ KEY_WIFI_ID+
     * "="+ TABLE_BOX+ "." + KEY_BOX_WIFI_ID+ " AND "+ TABLE_HISTORY_BOX_WIFI+
     * "." + KEY_HISTORY_BOX_WIFI_USER_ID+ "="+ TABLE_USER+ "." + KEY_USER_ID;
     * Cursor cusor = sql.rawQuery(querysql, null);
     * 
     * try { if (cusor.moveToFirst()) { do { HistoryWifimatchHome historywifi =
     * new HistoryWifimatchHome();
     * 
     * historywifi.setmBoxNameHistory(cusor.getString(0) .toString());
     * historywifi.setmDateTime(cusor.getString(1)); historywifi
     * .setmWifiId(Integer.parseInt(cusor.getString(2)));
     * historywifi.setmIconUserHistory(cusor.getString(3));
     * historywifi.setmMemberName(cusor.getString(4));
     * historywifi.setmNameWifiConnection(cusor.getString(5));
     * listWifiHistory.add(historywifi);
     * 
     * } while (cusor.moveToNext()); }
     * 
     * } catch (Exception e) {
     * 
     * } }
     */
    public ArrayList<HistoryWifimatchHome> getListWifiHistory() {
        Log.d("SQLite Helper History", "getListWifiHistory");
        SQLiteDatabase sql = this.getReadableDatabase();
        ArrayList<HistoryWifimatchHome> listWifiHistory = new ArrayList<HistoryWifimatchHome>();
        String querysql = " SELECT " + TABLE_BOX + "." + KEY_BOX_NAME + ","
                + TABLE_HISTORY_BOX_WIFI + "." + KEY_HISTORY_BOX_WIFI_DATETIME + ","
                + TABLE_HISTORY_BOX_WIFI + "." + KEY_HISTORY_BOX_WIFI_WIFI_ID + "," + TABLE_USER
                + "." + KEY_USER_ICON + "," + TABLE_USER + "." + KEY_USER_NAME + "," + TABLE_WIFI
                + "." + KEY_WIFI_NAME + " FROM "
                
                + TABLE_HISTORY_BOX_WIFI + "," + TABLE_BOX + "," + TABLE_USER + "," + TABLE_WIFI
                + " WHERE "
                
                + TABLE_HISTORY_BOX_WIFI + "." + KEY_HISTORY_BOX_WIFI_WIFI_ID + "=" + TABLE_BOX
                + "." + KEY_BOX_WIFI_ID + " AND " + TABLE_WIFI + "." + KEY_WIFI_ID + "="
                + TABLE_BOX + "." + KEY_BOX_WIFI_ID + " AND " + TABLE_HISTORY_BOX_WIFI + "."
                + KEY_HISTORY_BOX_WIFI_USER_ID + "=" + TABLE_USER + "." + KEY_USER_ID;
        Cursor cusor = sql.rawQuery(querysql, null);
        
        try {
            if (cusor.moveToFirst()) {
                do {
                    HistoryWifimatchHome historywifi = new HistoryWifimatchHome();
                    
                    historywifi.setmBoxNameHistory(cusor.getString(0).toString());
                    historywifi.setmDateTime(cusor.getString(1));
                    historywifi.setmWifiId(Integer.parseInt(cusor.getString(2)));
                    historywifi.setmIconUserHistory(cusor.getString(3));
                    historywifi.setmMemberName(cusor.getString(4));
                    historywifi.setmNameWifiConnection(cusor.getString(5));
                    listWifiHistory.add(historywifi);
                    
                } while (cusor.moveToNext());
            }
            
        } catch (Exception e) {
            
        }
        sql.close();
        
        return listWifiHistory;
        
    }
    
    /*
     * add data for history_action_member
     */
    public void addDataforActionMember(int _history_user_action_id,
            int _history_user_action_user_id, int _history_user_action_type,
            int _history_user_action_by_user_id, int _history_user_action_box_id,
            String _history_user_action_datetime) {
        Log.d("SQLite Helper History", "addDataforActionMember");
        ContentValues values = new ContentValues();
        values.put("_history_user_action_id", _history_user_action_id);
        values.put("_history_user_action_user_id", _history_user_action_user_id);
        values.put("_history_user_action_type", _history_user_action_type);
        values.put("_history_user_action_by_user_id", _history_user_action_by_user_id);
        values.put("_history_user_action_box_id", _history_user_action_box_id);
        values.put("_history_user_action_datetime", _history_user_action_datetime);
        
        sqlInsert(TABLE_HISTORY_USER_ACTION, null, values);
    }
    
    /*
     * add data for wifi match box History
     */
    
    public void addDataWifiMatchBox(int _history_box_wifi_id, int _history_box_wifi_box_id,
            int _history_box_wifi_wifi_id, int _history_box_wifi_user_id,
            int _history_box_wifi_type, String _history_box_wifi_status,
            String _history_box_wifi_datetime) {
        Log.d("SQLite Helper History", "addDataWifiMatchBox");
        ContentValues values = new ContentValues();
        values.put("_history_box_wifi_id", _history_box_wifi_id);
        values.put("_history_box_wifi_box_id", _history_box_wifi_box_id);
        values.put("_history_box_wifi_wifi_id", _history_box_wifi_wifi_id);
        values.put("_history_box_wifi_user_id", _history_box_wifi_user_id);
        values.put("_history_box_wifi_type", _history_box_wifi_type);
        values.put("_history_box_wifi_status", _history_box_wifi_status);
        values.put("_history_box_wifi_datetime", _history_box_wifi_datetime);
        
        sqlInsert(TABLE_HISTORY_BOX_WIFI, null, values);
    }
    
    /*
     * getData for
     */
    
    public ArrayList<HistoryMemberAction> getDataforActionMember() {
        Log.d("SQLite Helper History", "getDataforActionMember");
        String querysql1 = " SELECT " + TABLE_BOX + "." + KEY_BOX_NAME + "," + TABLE_USER + "."
                + KEY_USER_ICON + "," + TABLE_USER + "." + KEY_USER_NAME + ","
                + TABLE_HISTORY_USER_ACTION + "." + KEY_HISTORY_USER_ACTION_DATETIME + ","
                + TABLE_HISTORY_USER_ACTION + "." + KEY_HISTORY_USER_ACTION_TYPE + " FROM "
                
                + TABLE_HISTORY_USER_ACTION + "," + TABLE_BOX + "," + TABLE_USER + " WHERE "
                
                + TABLE_HISTORY_USER_ACTION + "." + KEY_HISTORY_USER_ACTION_USER_ID + "="
                + TABLE_USER + "." + KEY_USER_ID + " AND " + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_BOX_ID + "=" + TABLE_BOX + "." + KEY_BOX_ID;
        
        String querysql2 = " SELECT " + TABLE_USER + "." + KEY_USER_NAME + ","
                + TABLE_HISTORY_USER_ACTION + "." + KEY_HISTORY_USER_ACTION_BY_USER_ID + " FROM "
                
                + TABLE_HISTORY_USER_ACTION + "," + TABLE_USER + " WHERE "
                
                + TABLE_HISTORY_USER_ACTION + "." + KEY_HISTORY_USER_ACTION_BY_USER_ID + "="
                + TABLE_USER + "." + KEY_USER_ID;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cur1 = db.rawQuery(querysql1, null);
        Cursor cur2 = db.rawQuery(querysql2, null);
        ArrayList<HistoryMemberAction> listAction = new ArrayList<HistoryMemberAction>();
        try {
            if (cur1.moveToFirst() && cur2.moveToFirst()) {
                do {
                    HistoryMemberAction historyAction = new HistoryMemberAction();
                    
                    historyAction.setmBoxNameHistoryMember(cur1.getString(0));
                    historyAction.setmIconHistoryMember(cur1.getString(1));
                    historyAction.setmMemberNameHistory(cur1.getString(2));
                    historyAction.setmDateTimeHistoryMember(cur1.getString(3));
                    historyAction.setmByMemberNameHistory(cur2.getString(0));
                    historyAction.setmTypeAction(Integer.parseInt(cur1.getString(4)));
                    listAction.add(historyAction);
                    
                } while (cur1.moveToNext() && cur2.moveToNext());
            }
            
        } catch (Exception e) {
            
        }
        db.close();
        return listAction;
        
    }
    
    public String getDateTime(int itemDeviceId) {
        Log.d("SQLite Helper History", "getDateTime");
        String deviceDate = null;
        SQLiteDatabase db = this.getReadableDatabase();
        String sqlDate = "SELECT " + KEY_HISTORY_DEVICE_DATETIME + " FROM " + TABLE_HISTORY_DEVICE
                + " WHERE " + KEY_HISTORY_DEVICE_ID + "=" + itemDeviceId + " AND "
                + KEY_HISTORY_DEVICE_TYPE + "=0";
        Cursor cursor = db.rawQuery(sqlDate, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    DeviceItem item = new DeviceItem();
                    item.setDeviceDate(cursor.getString(0));
                    long millisecond = System.currentTimeMillis();
                    
                    try {
                        
                        millisecond = Long.parseLong(item.getDeviceDate());
                        
                    } catch (Exception e) {
                        
                    }
                    
                    Date date = new Date(millisecond);
                    
                    SimpleDateFormat df = new SimpleDateFormat("MMMM d, yyyy");
                    
                    deviceDate = df.format(date);
                    Log.d("opout time", "time: " + deviceDate);
                    
                } while (cursor.moveToNext());
            }
            return deviceDate;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
    }
    
    /*
     * insert data for History Box Wifi
     */
    public int insertWifiHistory(HistoryWifimatchHome item) {
        Log.d("SQLite Helper History", "insertWifiHistory");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_BOX_WIFI_BOX_ID, 1);
        values.put(KEY_HISTORY_BOX_WIFI_DATETIME, item.getmDateTime());
        values.put(KEY_HISTORY_BOX_WIFI_USER_ID, 1);
        values.put(KEY_HISTORY_BOX_WIFI_WIFI_ID, item.getmWifiId());
        values.put(KEY_HISTORY_BOX_WIFI_DATETIME, item.getmDateTime());
        values.put(KEY_HISTORY_BOX_WIFI_TYPE, 1);
        values.put(KEY_HISTORY_BOX_WIFI_STATUS, "on");
        
        return sqlInsert(TABLE_HISTORY_BOX_WIFI, null, values);
    }
    
    public int insertActionHistory(HistoryMemberAction item) {
        Log.d("SQLite Helper History", "insertActionHistory");
        ContentValues values = new ContentValues();
        values.put(KEY_HISTORY_USER_ACTION_BOX_ID, 1);
        values.put(KEY_HISTORY_USER_ACTION_USER_ID, 1);
        values.put(KEY_HISTORY_USER_ACTION_BY_USER_ID, 1);
        values.put(KEY_HISTORY_USER_ACTION_TYPE, item.getmTypeAction());
        values.put(KEY_HISTORY_USER_ACTION_DATETIME, item.getmDateTimeHistoryMember());
        
        return sqlInsert(TABLE_HISTORY_USER_ACTION, null, values);
    }
    
}
